package com.lexed.usersys.dao;

import java.sql.*;
import java.util.*;

import com.lexed.usersys.entity.User;
import com.lexed.usersys.utils.JDBCUtils;

/*
 * DAO 数据访问对象
 */
public class UserDAO {

	/**
	 * 验证用户登录功能
	 * 
	 * @throws SQLException
	 */
	public static User login(String name, String pwd) throws SQLException {
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		String sql = "select * from user where name=? and pwd=?;";
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 放入参数
		stmt.setString(1, name);
		stmt.setString(2, pwd);
		// 3 执行sql语句
		ResultSet rs = stmt.executeQuery();// 注意执行的时候不要再传入sql!
		if (rs.next()) {
			int id = rs.getInt(1);
			String n = rs.getString(2);
			String p = rs.getString(3);
			String sex = rs.getString(4);
			int age = rs.getInt(5);
			return new User(id, n, p, sex, age);
		}
		// 4. 释放资源
		JDBCUtils.destory(rs, stmt, conn);

		return null;

	}

	/**
	 * 添加用户功能
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public int insert(User u) throws SQLException {

		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		// 3. 执行sql语句
		String sql = "insert into user values (null,?,?,?,?);";
		PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setString(1, u.getName());
		stmt.setString(2, u.getPwd());
		stmt.setString(3, u.getSex());
		stmt.setInt(4, u.getAge());

		int result = stmt.executeUpdate();

		// 4. 释放资源
		JDBCUtils.destory(null, stmt, conn);

		return result;
	}

	/**
	 * 删除用户功能
	 * 
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public int deleteByName(String name) throws SQLException {

		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		String sql = "delete from user where name=?;";
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 3. 执行sql语句
		stmt.setString(1, name);
		int result = stmt.executeUpdate();
		// 4. 释放资源
		JDBCUtils.destory(null, stmt, conn);

		return result;
	}

	/**
	 * 修改用户功能
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public int update(User u) throws SQLException {

		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		String sql = "update user set name = ?,pwd = ?,sex = ?,age = ? where id=?;";
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 3. 执行sql语句
		stmt.setString(1, u.getName());
		stmt.setString(2, u.getPwd());
		stmt.setString(3, u.getSex());
		stmt.setInt(4, u.getAge());
		stmt.setInt(5, u.getId());
		int result = stmt.executeUpdate();
		// 4. 释放资源
		JDBCUtils.destory(null, stmt, conn);

		return result;
	}

	/**
	 * 列表用户功能
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<User> findAll() throws SQLException {

		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		Statement stmt = conn.createStatement();
		// 3. 执行sql语句
		String sql = "select * from user;";
		ResultSet rs = stmt.executeQuery(sql);
		System.out.println("用户id\t用户名\t密码\t  性别\t年龄");
		List<User> list = new ArrayList<>();
		while (rs.next()) {
			int id = rs.getInt("id"); // 如果传入String 通过列名获取
			String name = rs.getString("name");
			String pwd = rs.getString("pwd");
			String sex = rs.getString("sex");
			int age = rs.getInt("age");
			System.out.println(id + "\t" + name + "\t" + pwd + "\t  " + sex + "\t" + age);
			list.add(new User(id, name, pwd, sex, age));
		}
		// 4. 释放资源
		JDBCUtils.destory(null, stmt, conn);

		return list;
	}

	/**
	 * 根据用户名的部分字模糊查找用户功能
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<User> findByName(String name) throws SQLException {

		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		String sql = "select * from user where name like ?;";
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 3. 执行sql语句
		stmt.setString(1, "%" + name + "%");
		ResultSet rs = stmt.executeQuery();
		List<User> list = new ArrayList<>();
		while (rs.next()) {
			int tmpId = rs.getInt("id"); // 如果传入String 通过列名获取
			String tmpName = rs.getString("name");
			String pwd = rs.getString("pwd");
			String sex = rs.getString("sex");
			int age = rs.getInt("age");
			list.add(new User(tmpId, tmpName, pwd, sex, age));
		}
		// 4. 释放资源
		JDBCUtils.destory(null, stmt, conn);

		return list;
	}

}
